using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace HREDU.Hoang_Tuan.Classes
{
    /// <summary>
    /// Summary description for CDIADANH.
    /// </summary>
    public class CDIADANH
    {
        private int _id_diadanh;
        private string _nhom;
        private bool _cuoi;
        private string _ten_diadanh;
        private string _ten_xht;        //Chua xu ly vi neu thay doi ten Tinh thi truong TEN_XHT se phai thay doi ca Tinh cho Huyen, Xa

        public CDIADANH()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public int ID_DIADANH
        {
            get { return _id_diadanh; }
            set { _id_diadanh = value; }
        }

        public string NHOM
        {
            get { return _nhom; }
            set { _nhom = value; }
        }

        public bool CUOI
        {
            get
            {
                return _cuoi;
            }
            set
            {
                _cuoi = value;
            }
        }

        public string TEN_DIADANH
        {
            get { return _ten_diadanh; }
            set { _ten_diadanh = value; }
        }

        public string TEN_XHT
        {
            get { return _ten_xht; }
            set { _ten_xht = value; }
        }

        # region "Them, xoa, sua"

        public int ColumnLength(string column)
        {
            return (int)MITI.BaseDB.Instance.ExecSql_DataValue("select COL_LENGTH('DIADANH','" + column + "')") / 2;
        }

        public bool IsExists()
        {
            string strSQL = "select * from DIADANH where ten_diadanh =N'" + _ten_diadanh + "'";
            DataSet ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);
            if ((ds.Tables[0].Rows.Count > 0))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool IsUsed()
        {
            string strSQL = "select dbo.fc_IsDiaDanhUsed('" + _nhom.ToString() + "')";
            object kq = MITI.BaseDB.Instance.ExecSql_DataValue(strSQL);
            return (bool)kq;
        }

        public TINHTHANH TachTinhThanh()
        {
            TINHTHANH sTinhthanh;
            sTinhthanh.Tinh = "";
            sTinhthanh.Huyen = "";
            sTinhthanh.Xa = "";
            int len = _nhom.Length;
            sTinhthanh.Tinh = CDIADANHCollection.LayTheoNhom(_nhom.Substring(0, 2)).TEN_DIADANH;
            if (len >= 4)
                sTinhthanh.Huyen = CDIADANHCollection.LayTheoNhom(_nhom.Substring(0, 4)).TEN_DIADANH;
            if (len == 6)
                sTinhthanh.Xa = CDIADANHCollection.LayTheoNhom(_nhom.Substring(0, 6)).TEN_DIADANH;
            sTinhthanh.XaHuyenTinh = sTinhthanh.Xa + ", " + sTinhthanh.Huyen + ", " + sTinhthanh.Tinh;
            return sTinhthanh;
        }

        //Tra ve ten XHT khi doi tuong duoc cap nhat hay them moi
        public string GetTenXHT()
        {
            SqlCommand cmd = null;
            try
            {
                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@nhom", SqlDbType.NVarChar, 6);
                par[0].Value = _nhom;
                par[1] = new SqlParameter();
                par[1].Direction = ParameterDirection.ReturnValue;
                cmd = MITI.BaseDB.Instance.ExecSqlProcedure_Command("GetTenXHT", par);
                _ten_xht = cmd.Parameters[1].Value.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return _ten_xht;
        }
        # endregion
    }

    public class CDIADANHCollection
    {
        //Table Name trong CSDL
        public const string tableName = "DIADANH";
        //Cac thuoc tinh trong table
        public const string idColumnName = "id_diadanh";
        public const string nhomColumnName = "nhom";
        public const string cuoiColumnName = "cuoi";
        public const string tenColumnName = "ten_diadanh";
        public const string khacColumnName = "ten_xht";
        //Do dai ma Nhom
        public const int lenNhom = 2;
        //Ma ban dau
        //public const string maNhom = "01";
        //Cac ham procedure
        public const string procTaoNhom = "sp_HTTaoNhomDiaDanh";
        public const string procChuyenDoiNhom = "sp_HTChuyenDoiNhomDiaDanh";
        

        public static int ColumnLength(string column)
        {
            int len = int.Parse(MITI.BaseDB.Instance.ExecSql_DataValue("select COL_LENGTH('" + tableName + "','" + column + "')").ToString());
            return len / 2;
        }

        public static bool IsExists(string nhomCha, string ten, int cuoi)
        {
            string strSQL;
            strSQL = "select count(*) from " + tableName + " where left(" + nhomColumnName + "," + nhomCha.Length + ") = '" + nhomCha + "' and len(" + nhomColumnName + ") = " + (nhomCha.Length + lenNhom) + " and " + tenColumnName + " like N'" + ten + "' and " + cuoiColumnName + " = " + cuoi;
            int value = (int)MITI.BaseDB.Instance.ExecSql_DataValue(strSQL);
            if (value > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// Kiem tra Node dang chon co phai la node cuoi?
        /// </summary>
        /// <param name="nhom"></param>
        /// <returns></returns>
         
        public static bool IsChildNode(string nhom)
        {
            string strSQL;
            strSQL = "select count(*) from " + tableName + " where " + tenColumnName + " = '" + nhom + "' and " + cuoiColumnName + " = 1";
            int value = (int)MITI.BaseDB.Instance.ExecSql_DataValue(strSQL);
            if (value > 0)
            {
                return true;
            }
            return false;
        }

        public static bool IsExistsChildNodes(string nhomCha, bool bCuoi)
        {
            string strSQL = "";
            strSQL = "select count(*) from " + tableName + " where ";
            strSQL += " left(" + nhomColumnName + "," + nhomCha.Length.ToString() + ") = '" + nhomCha + "'";
            strSQL += " and len(" + nhomColumnName + ") > " + nhomCha.Length.ToString() + (bCuoi == true ? " and " + cuoiColumnName + " = 1" : "").ToString();
            int value = (int)MITI.BaseDB.Instance.ExecSql_DataValue(strSQL);
            if (value > 0)
            {
                return true;
            }
            return false;
        }

        public static void CountChildNodes(string nhomCha, ref int folder, ref int file)
        {
            string strSQL;
            strSQL = "select count(*) from " + tableName ;
            strSQL += " where left(" + nhomColumnName + "," + nhomCha.Length + ") = '" + nhomCha + "'";
            strSQL += " and len(" + nhomColumnName + ") = " + (nhomCha.Length + lenNhom).ToString();
            strSQL += " and " + cuoiColumnName + " = 0";

            strSQL += "select count(*) from " + tableName ;
            strSQL += " where left(" + nhomColumnName + "," + nhomCha.Length + ") = '" + nhomCha + "'";
            strSQL += " and len(" + nhomColumnName + ") = " + (nhomCha.Length + lenNhom).ToString();
            strSQL += " and " + cuoiColumnName + " = 1";
            DataSet ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);
            folder = (int)ds.Tables[0].Rows[0][0];
            file = (int)ds.Tables[1].Rows[0][0];
        }

        public static long Update(DataRow dr)
        {
            string strSQL;
            strSQL = "update " + tableName  + " set " + tenColumnName + " = " + dr[tenColumnName].ToString() + " " + cuoiColumnName + " = " + dr[cuoiColumnName].ToString() + " where " + nhomColumnName + " = " + dr[nhomColumnName].ToString();
            return MITI.BaseDB.Instance.ExecSql_NoneQuery(strSQL);
        }

        

        public static bool Insert(CDIADANH objDiaDanh)
        {
            StringBuilder objStringBuilder = new StringBuilder(250);
            {
                objStringBuilder.Append("INSERT INTO " + tableName + "(" + nhomColumnName + ", " + cuoiColumnName + ", " + tenColumnName + "," + khacColumnName + ") VALUES(");
                objStringBuilder.Append(MITI.BaseDB.Instance.GetParamPlaceHolder(nhomColumnName, true));
                objStringBuilder.Append(MITI.BaseDB.Instance.GetParamPlaceHolder(cuoiColumnName, true));
                objStringBuilder.Append(MITI.BaseDB.Instance.GetParamPlaceHolder(tenColumnName, true));
                objStringBuilder.Append(MITI.BaseDB.Instance.GetParamPlaceHolder(khacColumnName, false));
                objStringBuilder.Append(")");
            }

            string sInsert = objStringBuilder.ToString();

            SqlCommand cmd = MITI.BaseDB.Instance.CreateCommand(sInsert, CommandType.Text);
            long lRowsAffected = 0;
            try
            {
                {
                    cmd.Transaction = cmd.Connection.BeginTransaction();
                    AddParameter(ref cmd,nhomColumnName, objDiaDanh.NHOM);
                    AddParameter(ref cmd,cuoiColumnName, objDiaDanh.CUOI);
                    AddParameter(ref cmd, tenColumnName, objDiaDanh.TEN_DIADANH);
                    AddParameter(ref cmd, khacColumnName, objDiaDanh.TEN_XHT);  //Tao TEN_XHT khi TEN_DIADANH them vao
                    lRowsAffected = cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();
            }
            catch (System.Exception ex)
            {
                cmd.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                MITI.BaseDB.Instance.CloseConnect();
            }
            return true;
        }

        public static long Update(CDIADANH objDiaDanh)
        {
            long lRowsAffected = 0;
            string strSQL = "UPDATE " + tableName + " SET";
            strSQL += " " + nhomColumnName + " = " + MITI.BaseDB.Instance.GetParamPlaceHolder(nhomColumnName, true);
            strSQL += " " + cuoiColumnName + " = " + MITI.BaseDB.Instance.GetParamPlaceHolder(cuoiColumnName, true);
            strSQL += " " + tenColumnName + " = " + MITI.BaseDB.Instance.GetParamPlaceHolder(tenColumnName, true);
            strSQL += " " + khacColumnName + " = " + MITI.BaseDB.Instance.GetParamPlaceHolder(khacColumnName, false);
            strSQL += " WHERE " + idColumnName + " = " + MITI.BaseDB.Instance.GetParamPlaceHolder(idColumnName, false);
            
            SqlCommand cmd = MITI.BaseDB.Instance.CreateCommand(strSQL, CommandType.Text);
            try
            {
                    cmd.Transaction = cmd.Connection.BeginTransaction();
                    AddParameter(ref cmd,nhomColumnName, objDiaDanh.NHOM);
                    AddParameter(ref cmd,cuoiColumnName, objDiaDanh.CUOI);
                    AddParameter(ref cmd,tenColumnName, objDiaDanh.TEN_DIADANH);
                    AddParameter(ref cmd, khacColumnName, objDiaDanh.TEN_XHT);      //Cap nhat TEN_XHT khi TEN_DIADANH thay doi
                    AddParameter(ref cmd,idColumnName, objDiaDanh.ID_DIADANH);
                    lRowsAffected = cmd.ExecuteNonQuery();
                    cmd.Transaction.Commit();
            }
            catch (System.Exception ex)
            {
                cmd.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                MITI.BaseDB.Instance.CloseConnect();
            }
            return lRowsAffected;
        }

        public static long DeleteByNhom(string nhom, bool bDeleteAll)
        {
            if (bDeleteAll)
            {
                int lRowsAffected;
                string strSQL;
                strSQL = "delete from " + tableName + " where left(" + nhomColumnName + "," + nhom.Length.ToString() + ") = '" + nhom + "' and len(" + nhomColumnName + ") >= " + nhom.Length.ToString();
                lRowsAffected = (int)MITI.BaseDB.Instance.ExecSql_NoneQuery(strSQL);
                return lRowsAffected;
            }
            else
            {
                long lRowsAffected = 0;
                string strSQL = "delete from " + tableName + " where";
                strSQL += " " + nhomColumnName + " = " + MITI.BaseDB.Instance.GetParamPlaceHolder(nhomColumnName, false);
                SqlCommand cmd = MITI.BaseDB.Instance.CreateCommand(strSQL, CommandType.Text); 
                try
                {
                    cmd.Transaction = cmd.Connection.BeginTransaction();
                    AddParameter(ref cmd, nhomColumnName, nhom);
                    lRowsAffected = cmd.ExecuteNonQuery();
                    cmd.Transaction.Commit();
                }
                catch (System.Exception ex)
                {
                    cmd.Transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    MITI.BaseDB.Instance.CloseConnect();
                }
                return lRowsAffected;
            }
        }

        public static string TaoNhom(string nhomCha)
        {
            string nhom;
            SqlCommand cmd = null;
            try
            {
                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@nhomCha", SqlDbType.NVarChar, 6);
                par[0].Value = nhomCha;
                par[1] = new SqlParameter("@nhom", SqlDbType.NVarChar, 6);
                par[1].Direction = ParameterDirection.Output;
                cmd = MITI.BaseDB.Instance.ExecSqlProcedure_Command(procTaoNhom, par);
                nhom = cmd.Parameters["@nhom"].Value.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return nhom;
        }

        public static bool KiemTraLengthChuyenDoiNhom(string maDrag, string maDrop)
        {
            string strSQL = "select dbo.fc_HTKiemTraLengthChuyenDoiNhomDiaDanh('" + maDrag + "','" + maDrop + "')";
            return (bool)MITI.BaseDB.Instance.ExecSql_DataValue(strSQL);
        }

        public static string ChuyenDoiNhom(string maDrag, string maDrop)
        {
            SqlCommand cmd = null;
            try
            {
                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@maDrag", SqlDbType.NVarChar, 6);
                par[0].Value = maDrag;
                par[0].Direction = ParameterDirection.InputOutput;
                par[1] = new SqlParameter("@maDrop", SqlDbType.NVarChar, 6);
                par[1].Value = maDrop;
                cmd = MITI.BaseDB.Instance.ExecSqlProcedure_Command(procChuyenDoiNhom, par);
                maDrag = cmd.Parameters["@maDrag"].Value.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return maDrag;
        }

        public static CDIADANH LayTheoNhom(string nhom)
        {
            string strSQL;
            strSQL = "SELECT * FROM " + tableName  + " WHERE " + nhomColumnName + " = '" + nhom + "'";
            CDIADANH objDiaDanh = GetObjectBySQL(strSQL);
            return objDiaDanh;
        }

        public static CDIADANH LayTheoID(int id)
        {
            string strSQL;
            strSQL = "SELECT * FROM " + tableName  + " WHERE " + idColumnName + " = '" + id + "'";
            CDIADANH objDiaDanh = GetObjectBySQL(strSQL);
            return objDiaDanh;
        }

        public static CDIADANH LayTheoTen(string ten)
        {
            string strSQL;
            strSQL = "SELECT * FROM " + tableName  + " WHERE " + tenColumnName + " like N'" + ten + "'";
            CDIADANH objDiaDanh = GetObjectBySQL(strSQL);
            return objDiaDanh;
        }


        public static DataTable LayTheoNhom_DataTable(string nhom)
        {
            string strSQL;
            strSQL = "SELECT * FROM " + tableName  + " WHERE " + nhomColumnName + " = '" + nhom + "'";
            DataSet ds = null;
            try
            {
                ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return ds.Tables[0];
        }


        //Neu khong quan ly theo thuoc tinh CUOI, tuc khong loc theo thuoc tinh CUOI
        public static DataTable LayTheoNhomCha_DataTable(string nhomCha)
        {
            string strSQL;
            strSQL = "SELECT * from " + tableName  + " where left(" + nhomColumnName + ", " + nhomCha.Length.ToString() + ") = '" + nhomCha + "'" + " and len(" + nhomColumnName + ") = " + (nhomCha.Length + lenNhom).ToString() + " order by " + nhomColumnName + "";
            DataSet ds = null;
            try
            {
                ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return ds.Tables[0];
        }

        //Neu co quan ly theo thuoc tinh CUOI, tuc co loc theo thuoc tinh cuoi
        public static DataTable LayTheoNhomCha_DataTable(string nhomCha, int cuoi)
        {
            string strSQL;
            strSQL = "SELECT * from " + tableName  + " where left(" + nhomColumnName + ", " + nhomCha.Length.ToString() + ") = '" + nhomCha + "'" + " and len(" + nhomColumnName + ") = " + (nhomCha.Length + lenNhom).ToString() + " and " + cuoiColumnName + " = " + cuoi.ToString() + " and " + tenColumnName + " is not null order by " + nhomColumnName + "";
            //strSQL = "SELECT * from " + tableName  + " where left(" + nhomColumnName + ", " + nhomCha.Length.ToString() + ") = '" + nhomCha + "'" + " and len(" + nhomColumnName + ") = " + (nhomCha.Length + lenNhom).ToString() + " and cuoi = " + cuoi.ToString() + " order by " + nhomColumnName + "";
            DataSet ds = null;
            try
            {
                ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return ds.Tables[0];
        }

        public static DataTable TimTheoTen_DataTable(string tenCanTim, string nhom)
        {
            string strSQL;
            strSQL = "SELECT * FROM " + tableName  + " WHERE " + tenColumnName + " like N'" + tenCanTim + "%'";

            if (nhom != "")
            {
                strSQL += " and left(" + nhomColumnName + "," + nhom.Length.ToString() + ") = '" + nhom + "'" + " and len(" + nhomColumnName + ") > " + nhom.Length.ToString();
            }

            return MITI.BaseDB.Instance.ExecSql_DataSet(strSQL).Tables[0];
        }

        public static bool LayGiaTriCuoiTheoNhom(string nhom)
        {
            string strSQL = "";
            strSQL = "select isnull(" + cuoiColumnName + ",0) from " + tableName + " where " + nhomColumnName + " ='" + nhom + "'";
            bool kq;
            try
            {
                kq = (bool)MITI.BaseDB.Instance.ExecSql_DataValue(strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return kq;
        }

        /// <summary>
        /// Adds a new parameter to the specified command.
        /// </summary>
        /// <param name="cmd">The <see cref="System.Data.SqlClient.SqlCommand"/> object to add the parameter to.</param>
        /// <param name="paramName">The name of the parameter.</param>
        /// <param name="value">The value of the parameter.</param>
        /// <returns>A reference to the added parameter.</returns>
        protected static SqlParameter AddParameter(ref SqlCommand cmd, string paramName, object value)
        {
            SqlParameter parameter = null ;
            switch (paramName)
            {
                case idColumnName:
                    parameter = MITI.BaseDB.Instance.CreateParameter(ref cmd, paramName, DbType.Int16, (value == null ? DBNull.Value : value));
                    break;
                case nhomColumnName:
                case tenColumnName:
                case khacColumnName:
                    parameter = MITI.BaseDB.Instance.CreateParameter(ref cmd, paramName, DbType.String, (value == null ? DBNull.Value : value));
                    break;
                case cuoiColumnName:
                    parameter = MITI.BaseDB.Instance.CreateParameter(ref cmd, paramName, DbType.Boolean, (value == null ? DBNull.Value : value));
                    break;
            }
            return parameter;
        }

        public static CDIADANH GetObjectBySQL(string strSQL)
        {
            SqlDataReader objReader = null;
            CDIADANH objDiaDanh=null;
            try
            {
                objReader = MITI.BaseDB.Instance.ExecSql_DataReader(strSQL);
                while (objReader.Read())
                {
                     objDiaDanh= GetObjectByDataReader(objReader);
                }
                if ((objReader != null))
                {
                    objReader.Close();
                }
                MITI.BaseDB.Instance.CloseConnect();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return objDiaDanh;
        }

        public static CDIADANH GetObjectByDataReader(SqlDataReader objReader)
        {
            CDIADANH objDiaDanh = new CDIADANH();
            objDiaDanh.ID_DIADANH = objReader.GetInt16(0);
            objDiaDanh.NHOM = objReader.GetString(1);
            objDiaDanh.CUOI = objReader.GetBoolean(2);
            objDiaDanh.TEN_DIADANH = objReader.GetString(3);
            objDiaDanh.TEN_XHT = (objReader.GetValue(4) == DBNull.Value ? null : objReader.GetString(4));
            return objDiaDanh;
        }
    }
}	
